﻿/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/** Create Table **/

if not exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}YourCompany_ModuleName]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		CREATE TABLE {databaseOwner}[{objectQualifier}YourCompany_ModuleName]
		(
			[ModuleID] [int] NOT NULL,
			[ItemID] [int] NOT NULL IDENTITY(1, 1),
			[Content] [ntext] NOT NULL,
			[CreatedByUser] [int] NOT NULL,
			[CreatedDate] [datetime] NOT NULL
		)

		ALTER TABLE {databaseOwner}[{objectQualifier}YourCompany_ModuleName] ADD CONSTRAINT [PK_{objectQualifier}YourCompany_ModuleName] PRIMARY KEY CLUSTERED  ([ItemID])
		CREATE NONCLUSTERED INDEX [IX_{objectQualifier}YourCompany_ModuleName] ON {databaseOwner}[{objectQualifier}YourCompany_ModuleName] ([ModuleID])

		ALTER TABLE {databaseOwner}[{objectQualifier}YourCompany_ModuleName] WITH NOCHECK ADD CONSTRAINT [FK_{objectQualifier}YourCompany_ModuleName_{objectQualifier}Modules] FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION
	END
GO


/** Drop Existing Stored Procedures **/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}YourCompany_GetModuleNames]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}YourCompany_GetModuleNames
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}YourCompany_GetModuleName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}YourCompany_GetModuleName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}YourCompany_AddModuleName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}YourCompany_AddModuleName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}YourCompany_UpdateModuleName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}YourCompany_UpdateModuleName
GO

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}YourCompany_DeleteModuleName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure {databaseOwner}{objectQualifier}YourCompany_DeleteModuleName
GO

/** Create Stored Procedures **/


create procedure {databaseOwner}{objectQualifier}YourCompany_GetModuleNames

	@ModuleId int

as

select ModuleId,
       ItemId,
       Content,
       CreatedByUser,
       CreatedDate
from {objectQualifier}YourCompany_ModuleName with (nolock)
left outer join {objectQualifier}Users on {objectQualifier}YourCompany_ModuleName.CreatedByUser = {objectQualifier}Users.UserId
where  ModuleId = @ModuleId
GO

create procedure {databaseOwner}{objectQualifier}YourCompany_GetModuleName

	@ModuleId int,
        @ItemId int

as

select ModuleId,
       ItemId,
       Content,
       CreatedByUser,
       CreatedDate
from {objectQualifier}YourCompany_ModuleName with (nolock)
left outer join {objectQualifier}Users on {objectQualifier}YourCompany_ModuleName.CreatedByUser = {objectQualifier}Users.UserId
where  ModuleId = @ModuleId
and ItemId = @ItemId
GO


create procedure {databaseOwner}{objectQualifier}YourCompany_AddModuleName

	@ModuleId       int,
	@Content        ntext,
	@UserID         int

as

insert into {objectQualifier}YourCompany_ModuleName (
	ModuleId,
	Content,
	CreatedByUser,
	CreatedDate
) 
values (
	@ModuleId,
	@Content,
	@UserID,
	getdate()
)

GO

create procedure {databaseOwner}{objectQualifier}YourCompany_UpdateModuleName

	@ModuleId       int,
        @ItemId         int,
	@Content        ntext,
	@UserID         int

as

update {objectQualifier}YourCompany_ModuleName
set    Content       = @Content,
       CreatedByUser = @UserID,
       CreatedDate   = getdate()
where  ModuleId = @ModuleId
and    ItemId = @ItemId

GO

create procedure {databaseOwner}{objectQualifier}YourCompany_DeleteModuleName

	@ModuleId       int,
        @ItemId         int

as

delete
from   {objectQualifier}YourCompany_ModuleName
where  ModuleId = @ModuleId
and    ItemId = @ItemId

GO

/************************************************************/
/*****              SqlDataProvider                     *****/
/************************************************************/